import connector.driver
from connector.driver import DatabaseDriver


def get_all_books(driver: DatabaseDriver) -> tuple:
    results: tuple = driver.query_all("select * from book")
    return results


# 根据用户的字符串输入查找图书结果
def user_search_books(driver: DatabaseDriver, key: str) -> tuple:
    results: tuple = driver.query_all(
        "select * from book where book_name like '%{}%' or publisher like '%{}%' or author like '%{}%'".format(key, key
                                                                                                               , key))
    return results


# 根据书籍名、作者、书籍类型检索图书
def get_book_by_name_author_type(driver: DatabaseDriver, book_name: str = "", author: str = "",
                                 book_type: tuple = None) -> tuple:
    if book_name == "" and author == "" and book_type == "":
        return get_all_books()
    else:
        if len(book_type) > 0:
            book_type = book_type[0]
        sql: str = ""
        if book_name != "" and author != "" and book_type is not None:
            sql: str = "select * from book where book_name like '%{}%' and author like '%{}%' and cate_id={}".format(
                book_name,
                author,
                book_type)
        elif book_name != "" and author != "":
            sql: str = "select * from book where book_name like '%{}%' and author like '%{}%'".format(book_name, author)
        elif book_name != "" and book_type is not None:
            sql: str = "select * from book where book_name like '%{}%' and cate_id={}".format(book_name, book_type)
        elif author != "" and book_type is not None:
            sql: str = "select * from book where author like '%{}%' and cate_id={}".format(author, book_type)
        elif book_name != "":
            sql: str = "select * from book where book_name like '%{}%'".format(book_name)
        elif book_type != "":
            sql: str = "select * from book where cate_id={}".format(book_type)
        elif author != "":
            sql: str = "select * from book where author like '%{}%'".format(author)
        return driver.query_all(sql=sql, log=True)


# 如果用户输入的是数字，则用这个方式查找图书
def user_search_books_by_book_id(driver: DatabaseDriver, book_id: int) -> tuple:
    results: tuple = driver.query_all('select * from book where book_id={}'.format(book_id))
    return results


# 根据用户id查询当前用户所借阅没有归还的书籍
# 注意，这里采用了外键查询，用右连接连接了书本表，这样做的目的是在于能够根据借阅表当中的user_id查询出借的书本的具体信息。
def get_borrow_books_by_user_id(driver: DatabaseDriver, user_id: int) -> list:
    # select borrow.*,book.book_name,book.author,book.save_loc from book,borrow where book.book_id = borrow.book_id and borrow.user_id=1 and return_time is NULL
    return driver.query_all(
        "select borrow.*,book.book_name,book.author,book.save_loc from book,borrow where book.book_id = borrow.book_id and borrow.user_id={} and return_time is NULL".format(
            user_id))


# 查询书名是否重复
def user_search_books_by_book_name(driver: DatabaseDriver, book_name: str):
    return driver.query_all('select * from book where book_name = "{}"'.format(book_name))


# 更新书本
def update_book(driver: DatabaseDriver, book_id:int, book_name:str, book_publisher:str, book_author:str, cate_id:int, nums:int, save_loc:str)->int:
    return driver.update('update book set book_name="{}",publisher="{}",author="{}",cate_id={},nums={},save_loc="{}" where book_id={}'.format(book_name,book_publisher,book_author,cate_id,nums,save_loc,book_id))

# 插入新的书本
def insert_book(driver: DatabaseDriver, book_name:str, book_publisher:str, book_author:str, cate_id:int, nums:int, save_loc:str)->int:
    return driver.insert('insert into book(book_name,publisher,author,cate_id,nums,save_loc) values ("{}","{}","{}",{},{},"{}")'.format(
        book_name,book_publisher,book_author,cate_id,nums,save_loc
    ))